if exists (select * from sys.objects where object_id = OBJECT_ID(N'[dbo].[cms_udf_VectorizeHierarchy]') and type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
drop function [dbo].[cms_udf_VectorizeHierarchy]
go

create function [dbo].[cms_udf_VectorizeHierarchy]
(
	@i hierarchyID
)
returns @vect table 
(
	position int identity(1,1) not null,
	nodeValue int not null
)
as begin
  declare @list varchar(max) = @i.ToString()
  
  declare @delimit int
  
  set @list = substring(@list,2,len(@list)-1)
  
  while len(@list) > 1 
  begin
    set @delimit = charindex('/',@list)
	
    insert into @vect values (cast(substring(@list,1, @delimit - 1) as int))
    set @list = substring(@list,@delimit + 1, len(@list))
  end
  return
end

go
